﻿using ExcelDataReader;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WJP.Utils.Excels;

namespace WJP.Excel
{
    /// <summary>
    /// Excel操作入口
    /// </summary>
    public class ExcelPort : AbstractExcelPort
    {
        public static IDictionary<string, ICellStyle> StyleCollection { get; set; } = new Dictionary<string, ICellStyle>();

        public ExcelType Type { get; set; } = ExcelType.Excel2003;

        ExcelReaderConfiguration ExcelReaderConfiguration { get; set; }

        ExcelDataSetConfiguration ExcelDataSetConfiguration { get; set; }

        public ExcelPort()
        {
            ExcelReaderConfiguration = new ExcelReaderConfiguration();
            ExcelDataSetConfiguration = new ExcelDataSetConfiguration();
        }

        /// <summary>
        /// 读取<see cref="Stream"/>保存为<see cref="DataSet"/>
        /// <para>支持的文件类型为：.xls  .xlsx  .csv</para>
        /// </summary>
        /// <param name="stream">文件的字节流</param>
        /// <returns></returns>
        public override DataSet ReaderAsSet(Stream stream)
        {
            IExcelDataReader reader = null;
            try
            {
                if (Type == ExcelType.CSV)
                    reader = ExcelReaderFactory.CreateCsvReader(stream, ExcelReaderConfiguration);
                else
                    reader = ExcelReaderFactory.CreateReader(stream, ExcelReaderConfiguration);

                return reader.AsDataSet(ExcelDataSetConfiguration);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                reader.Close();
            }
        }

        public void SetReaderConfig(Action<ExcelReaderConfiguration> action = null)
        {
            action?.Invoke(ExcelReaderConfiguration);
        }

        public void SetDataSetConfig(Action<ExcelDataSetConfiguration> action = null)
        {
            action?.Invoke(ExcelDataSetConfiguration);
        }

        /// <summary>
        /// 保存一个DataSet中的多个表到目的Excel文件中，每个Sheet名为对应Table的名字。
        /// </summary>
        /// <param name="dataSet">包含多个表的数据集</param>
        /// <param name="fileName">目的文件地址，如果已经存在，就会覆盖。</param>
        public override void SaveToFile(DataSet dataSet, string fileName)
        {
            CheckFileName(fileName);
            InnerSave(dataSet, fileName, false);
        }

        private void InnerSave(DataSet dataSet, string outputPath, bool deleteOldFile)
        {
            if (Type == ExcelType.CSV)
                throw new NotSupportedException("暂不支持导出.csv文件类型");

            Dictionary<Type, ICellStyle> styles = new Dictionary<Type, ICellStyle>();
            IWorkbook workbook = null;
            try
            {
                if (deleteOldFile && File.Exists(outputPath))
                    File.Delete(outputPath);

                workbook = Type != ExcelType.Excel2003 ? 
                    new XSSFWorkbook() : 
                    (IWorkbook)new HSSFWorkbook();
                foreach (DataTable table in dataSet.Tables)
                {
                    int rowCount = table.Rows.Count;
                    int columnCount = table.Columns.Count;
                    ISheet sheet = workbook.CreateSheet(table.TableName);
                    IRow headRow = sheet.CreateRow(0);
                    for (int column = 0; column < columnCount; ++column)
                    {
                        ICell cell = headRow.CreateCell(column);
                        cell.CellStyle = GetCellStyle(workbook, sheet.SheetName, Stylexls.Header);
                        cell.SetCellValue(table.Columns[column].ColumnName);
                    }
                    for (int index = 0; index < rowCount; ++index)
                    {
                        IRow row = sheet.CreateRow(index + 1);
                        DataRow dataRow = table.Rows[index];
                        for (int column = 0; column < columnCount; ++column)
                        {
                            ICell cell = row.CreateCell(column);
                            object obj = dataRow[column];
                            if (obj != null)
                                cell.CellStyle = GetCellStyle(styles, obj.GetType(), workbook, sheet.SheetName, Stylexls.Default);
                            SetValue(cell, dataRow[column]);
                        }
                    }

                    //列宽自适应，只对英文和数字有效
                    for (int i = 0; i <= columnCount; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }

                    //获取当前列的宽度，然后对比本列的长度，取最大值
                    for (int columnNum = 0; columnNum <= columnCount; columnNum++)
                    {
                        int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                        for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
                        {
                            IRow currentRow;
                            //当前行未被使用过
                            if (sheet.GetRow(rowNum) == null)
                            {
                                currentRow = sheet.CreateRow(rowNum);
                            }
                            else
                            {
                                currentRow = sheet.GetRow(rowNum);
                            }

                            if (currentRow.GetCell(columnNum) != null)
                            {
                                ICell currentCell = currentRow.GetCell(columnNum);
                                int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                                if (columnWidth < length)
                                {
                                    columnWidth = length;
                                }
                            }
                        }
                        sheet.SetColumnWidth(columnNum, columnWidth * 256);
                    }

                    using (FileStream fileStream = File.OpenWrite(outputPath))
                        workbook.Write(fileStream);
                }
            }
            finally
            {
                StyleCollection.Clear();
            }
        }

        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="styles"></param>
        /// <param name="type"></param>
        /// <param name="wb"></param>
        /// <param name="sheetName"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(Dictionary<Type, ICellStyle> styles, Type type, IWorkbook wb, string sheetName, Stylexls str)
        {
            if (!styles.TryGetValue(type, out ICellStyle cellStyle))
            {
                cellStyle = GetCellStyle(wb, sheetName, str);
                styles.Add(type, cellStyle);
            }
            return cellStyle;
        }

        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="stylexls">样式类型</param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook, string sheetName, Stylexls stylexls)
        {
            if (StyleCollection.TryGetValue(sheetName + stylexls.ToString(), out ICellStyle cellStyle))
                return cellStyle;

            cellStyle = workbook.CreateCellStyle();
            IFont headerFont = workbook.CreateFont();
            headerFont.FontHeightInPoints = 10;
            headerFont.FontName = "微软雅黑";
            IFont defaultFont = workbook.CreateFont();
            defaultFont.FontName = "微软雅黑";
            IFont urlFont = workbook.CreateFont();
            urlFont.Color = 12;
            urlFont.IsItalic = true;
            urlFont.FontName = "微软雅黑";
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.Alignment = HorizontalAlignment.Left;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.WrapText = true;
            cellStyle.Indention = 0;

            switch (stylexls)
            {
                case Stylexls.Header:
                    cellStyle.SetFont(headerFont);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;
                case Stylexls.Url:
                    urlFont.Underline = FontUnderlineType.Single;
                    cellStyle.SetFont(urlFont);
                    break;
                case Stylexls.Date:
                    IDataFormat dataFormat1 = workbook.CreateDataFormat();
                    cellStyle.DataFormat = dataFormat1.GetFormat("yyyy/mm/dd");
                    cellStyle.SetFont(defaultFont);
                    break;
                case Stylexls.Numeric:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.SetFont(defaultFont);
                    break;
                case Stylexls.Money:
                    IDataFormat dataFormat2 = workbook.CreateDataFormat();
                    cellStyle.DataFormat = dataFormat2.GetFormat("￥#,##0");
                    cellStyle.SetFont(defaultFont);
                    break;
                case Stylexls.Percent:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(defaultFont);
                    break;
                case Stylexls.CHSUpper:
                    IDataFormat dataFormat3 = workbook.CreateDataFormat();
                    cellStyle.DataFormat = dataFormat3.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(defaultFont);
                    break;
                case Stylexls.ScientificNotation:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(defaultFont);
                    break;
                case Stylexls.Default:
                    cellStyle.SetFont(defaultFont);
                    break;
            }

            StyleCollection.Add(sheetName + stylexls.ToString(), cellStyle);
            return cellStyle;
        }

        /// <summary>
        /// 设置ICell的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="value"></param>
        public void SetValue(ICell cell, object value)
        {
            if (value is bool)
                cell.SetCellValue((bool)value);
            else if (IsNumber(value))
                cell.SetCellValue(value.ConvertTo<double>());
            else
                cell.SetCellValue(value?.ToString());
        }

        /// <summary>
        /// object是否为数值类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private bool IsNumber(object value)
        {
            return value is sbyte || value is byte || (value is short || value is ushort) || (value is int || value is uint || (value is long || value is ulong)) || (value is float || value is double) || value is decimal;
        }

    }
}
